package com.yu.dv.service;

import com.yu.dv.autoconfigure.properties.NaturalLanguageSqlProperties;
import com.yu.dv.entity.SqlTransferException;
import com.yu.dv.entity.res.ExecutionResult;
import lombok.extern.slf4j.Slf4j;
import org.springframework.jdbc.core.JdbcTemplate;

import javax.sql.DataSource;
import java.sql.ResultSetMetaData;
import java.util.*;

@Slf4j
public class SqlExecutor {

    private final JdbcTemplate jdbcTemplate;
    private final NaturalLanguageSqlProperties properties;

    public SqlExecutor(DataSource dataSource, NaturalLanguageSqlProperties properties) {
        this.jdbcTemplate = new JdbcTemplate(dataSource);
        this.properties = properties;
    }

    public ExecutionResult executeDdl(String sql) {
        if (sql == null || sql.trim().isEmpty()) {
            return ExecutionResult.error("SQL语句为空");
        }

        String trimmedSql = sql.trim();
        String upperSql = trimmedSql.toUpperCase();

        try {
            if (isQuery(upperSql)) {
                return executeQuery(trimmedSql);
            }

            if (isDml(upperSql)) {
                if (properties.isEnableDml()) {
                    return executeUpdate(trimmedSql);
                } else {
                    throw new SqlTransferException("DML操作被禁用，请检查yu.dv.sql.enable-dml配置");
                }
            }

            if (isDdl(upperSql)) {
                if (properties.isEnableDdl()) {
                    return executeUpdate(trimmedSql);
                } else {
                    throw new SqlTransferException("DDL操作被禁用，请检查yu.dv.sql.enable-ddl配置");
                }
            }

            throw new SqlTransferException("不支持的SQL语句类型: " + sql);

        } catch (Exception e) {
            return ExecutionResult.error("SQL执行失败: " + e.getMessage());
        }
    }

    private ExecutionResult executeQuery(String sql) {
        try {
            List<Map<String, Object>> result = jdbcTemplate.query(sql, rs -> {
                List<Map<String, Object>> resultList = new ArrayList<>();
                ResultSetMetaData metaData = rs.getMetaData();
                int columnCount = metaData.getColumnCount();

                while (rs.next()) {
                    Map<String, Object> row = new LinkedHashMap<>();
                    for (int i = 1; i <= columnCount; i++) {
                        String columnName = metaData.getColumnName(i);
                        Object value = rs.getObject(i);
                        row.put(columnName, value);
                    }
                    resultList.add(row);
                }
                return resultList;
            });

            Map<String, Object> data = new HashMap<>();
            data.put("type", "QUERY");
            data.put("result", result);
            data.put("rowCount", result.size());
            data.put("executedSql", sql);

            return ExecutionResult.success("查询执行成功，返回 " + result.size() + " 行数据", data);

        } catch (Exception e) {
            return ExecutionResult.error("查询执行失败: " + e.getMessage());
        }
    }

    private ExecutionResult executeUpdate(String sql) {
        try {
            int affectedRows = jdbcTemplate.update(sql);
            Map<String, Object> data = new HashMap<>();
            data.put("type", "UPDATE");
            data.put("affectedRows", affectedRows);
            data.put("executedSql", sql);
            return ExecutionResult.success("更新执行成功，影响 " + affectedRows + " 行", data);
        } catch (Exception e) {
            return ExecutionResult.error("更新执行失败: " + e.getMessage());
        }
    }

    private boolean isQuery(String sql) {
        return sql.startsWith("SELECT ") || sql.startsWith("SHOW ") ||
                sql.startsWith("DESC ") || sql.startsWith("DESCRIBE ") ||
                sql.startsWith("WITH "); // 支持CTE
    }

    private boolean isDml(String sql) {
        return sql.startsWith("INSERT ") || sql.startsWith("UPDATE ") ||
                sql.startsWith("DELETE ") || sql.startsWith("REPLACE ") ||
                sql.startsWith("MERGE ");
    }

    private boolean isDdl(String sql) {
        return sql.startsWith("CREATE ") || sql.startsWith("ALTER ") ||
                sql.startsWith("DROP ") || sql.startsWith("TRUNCATE ") ||
                sql.startsWith("RENAME ") || sql.startsWith("GRANT ") ||
                sql.startsWith("REVOKE ") || sql.startsWith("COMMENT ");
    }

    /**
     * 测试SQL语法（不实际执行）
     */
    public ExecutionResult validateSql(String sql) {
        if (sql == null || sql.trim().isEmpty()) {
            return ExecutionResult.error("SQL语句为空");
        }

        try {
            Map<String, Object> data = new HashMap<>();
            data.put("originalSql", sql);
            data.put("sqlType", getSqlType(sql));

            return ExecutionResult.success("SQL语法验证通过", data);
        } catch (Exception e) {
            return ExecutionResult.error("SQL语法验证失败: " + e.getMessage());
        }
    }

    private String getSqlType(String sql) {
        String upperSql = sql.trim().toUpperCase();
        if (isQuery(upperSql)) return "QUERY";
        if (isDml(upperSql)) return "DML";
        if (isDdl(upperSql)) return "DDL";
        return "UNKNOWN";
    }
}